Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

Load fields from file using conditions

Hi all .

I have a table from which I need to load a fields. For example table |I have like this one:.

NumABCF1F2FN
1222724u  
2222724 uu
3222325  u

 

From this tableI need to Load field "Num", all fields starting with F and A or B or C.

Also I have an internal parameter vParam that may be equal  "A"  or "B" or "Null()".  In case if vParam  = A, than I should load field "Num", field "A" field (and not B or C) and all fields starting with F,  if vParam = B, then "Num", field B,all fields starting with F .  And if vParam  = Null() then loading "Num",  C, all fields starting with F .

Please, help me to understood how I may do Load from file with conditions I've described?

 

P.S.

If  vParam = A resulted  table should be like 

NumAF1F2FN
122u  
222 uu
322  u

 

If  vParam = B resulted  table should be like

NumBF1F2FN
127u  
227 uu
323  u

 

If  vParam = C resulted  table should be like

NumCF1F2FN
124u  
224 uu
325  u
Labels (1)
1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

Hi @Peony , 

Thanks for the update question. I think I've got your answer. 

// Create table with paramater values to loop over
ParamTable:
LOAD * INLINE [
ParamField
A
B
C
];


// Set basetable
BaseTable:
LOAD * INLINE [
Num, A, B, C, F1, F2, FN
1, 22, 27, 24, u, , ,
2, 22, 27, 24, , u, u,
3, 22, 23, 25, , , u,
];


// Set loop
FOR i = 0 TO NUM(NOOFROWS('ParamTable')-1);

// Set variable with paramater value
LET vParam = PEEK('ParamField',$(i),'ParamTable');


Table_A:
LOAD
Num,
'$(vParam)' AS ParamValue, // Get the paramater value (A, B or C) to see which field is fetched
$(vParam) AS ParamResult, // Get the field value from field A, B, or C
F1,
F2,
FN
RESIDENT BaseTable;

NEXT i;


DROP TABLE BaseTable,ParamTable;

 

This results in the following table.

NumParamResultParamValueF1F2FN
122Au  
322A  u
222A uu
127Bu  
323B  u
227B uu
124Cu  
325C  u
224C uu

View solution in original post

4 Replies
avkeep01
Partner - Specialist
Partner - Specialist

Hi @Peony , 

It isn't really clear what you want to do. But I think I can help you a bit further by directing you to a crosstable load. 

CROSSTABLE (Character,Number) LOAD * INLINE [
Num, A, B, C, F1, F2, FN
1, 22, 27, 24, u, , ,
2, 22, 27, 24, , u, u,
3, 22, 23, 25, , , u,
];

In that case the result will be like this. And that should make it more easy for you to make your selections. 

Num Character Number 
1F2 
1FN 
2F1 
3F1 
3F2 
1F1u
2F2u
2FNu
3FNu
1A22
2A22
3A22
3B23
1C24
2C24
3C25
1B27
2B27
Peony
Creator III
Creator III
Author

Hi @avkeep01 . Thank you for trying to help me. 
Unfortunately, your solution is not the result I need. I added some changes to the my post. Hopefully now it’s clearer what I'm looking for as a result.

avkeep01
Partner - Specialist
Partner - Specialist

Hi @Peony , 

Thanks for the update question. I think I've got your answer. 

// Create table with paramater values to loop over
ParamTable:
LOAD * INLINE [
ParamField
A
B
C
];


// Set basetable
BaseTable:
LOAD * INLINE [
Num, A, B, C, F1, F2, FN
1, 22, 27, 24, u, , ,
2, 22, 27, 24, , u, u,
3, 22, 23, 25, , , u,
];


// Set loop
FOR i = 0 TO NUM(NOOFROWS('ParamTable')-1);

// Set variable with paramater value
LET vParam = PEEK('ParamField',$(i),'ParamTable');


Table_A:
LOAD
Num,
'$(vParam)' AS ParamValue, // Get the paramater value (A, B or C) to see which field is fetched
$(vParam) AS ParamResult, // Get the field value from field A, B, or C
F1,
F2,
FN
RESIDENT BaseTable;

NEXT i;


DROP TABLE BaseTable,ParamTable;

 

This results in the following table.

NumParamResultParamValueF1F2FN
122Au  
322A  u
222A uu
127Bu  
323B  u
227B uu
124Cu  
325C  u
224C uu
Peony
Creator III
Creator III
Author

@avkeep01  Yes, you got may idea correct! Thank you for help!