Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
DEMONIO_AZUL
Contributor III
Contributor III

Create only missing values in a table

Hello there!

Is there a way to load a table with a load script and then append the missing values using another script section that creates them?

This is what I mean:

The original table is something like this:

Location Code
Madrid A0

Barcelona

A1
Toledo A2
London B3
Paris C2
Osaka D5

 

But I need to append to the table above all values that are missing, namely:

  • for letter A, from A3 to A9;
  • for letter B, from B0 to B2 and from B4 to B9;
  • for letter C, from C0 to C1 and from C3 to C9; 
  • for letter D, from D0 to D4 and from D6 to D9; and
  • for letter E, from E0 to E9 (because it is still not used).

For all appended Codes, the location may be filled with a blank or "not used".

Thanks in advance for all your help.

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@DEMONIO_AZUL  try below. For all not existing codes location will be NULL. If you want some values instead, you can take resident of this table create a column with below condition

if(len(trim(Location))=0,'Not defined',Location) as Location_Final

Data:
Load * Inline [
Location, Code
Madrid, A900
Barcelona, A107
Toledo, A201
London, B327
Paris, C255
Osaka, D566
];

Concatenate(Data)
Load *
where not Exists(Code);
Load Letter & num(From + IterNo()-1,'000') as Code
while IterNo()-1<=To;
Load * Inline [
Letter,From,To
A,0,999
B,0,999
C,0,999
D,0,999
E,0,999 ];

View solution in original post

7 Replies
Qrishna
Master
Master

As long as your both tables' field names and number of fields match, the latter table automatically gets appended to the former table: try below:

 

ExistingData:
load * inline [
Location, Code
Madrid, A0
Barcelona,A1
Toledo, A2
London, B3
Paris, C2
Osaka, D5
];


AppendData:
Load * inline [
Location, Code
,A3
,A4
,A5
,A6
,A7
,A8
,A9
,B4
,B5
,C3
,C4
,D6
,D7
,E0
,E1
];

 

before:

2489522 - Create only missing values in a table - Concatenate in script - Before.PNG

 

After:

2489522 - Create only missing values in a table - Concatenate in script - After.PNG

DEMONIO_AZUL
Contributor III
Contributor III
Author

Thanks, Qrishna. This works for the example table that I provided.The sample table is a simplified one where I tried to explain the situation without complicated structures.

My bad, I was not clear enough. While I included the word “create” at this question’s title, I was not specific in telling these relevant topics:

1. I may get a table with hundreds of rows and the field Code may be A000 to A999. 

2. I won’t know which rows are missing. 
3. Due to 1  & 2, it is impossible to input manually a table with the missing rows. 
4. Thus, I need to create automatically at load script the table to be appended. That table should have the missing rows.

Hope this is clear. And hopefully you can help me further.

Thanks again!

Kushal_Chawda

@DEMONIO_AZUL  So when you Say A3 to A9 means, A003 to A999? Could you be specific?

DEMONIO_AZUL
Contributor III
Contributor III
Author

Yes, Kushal, that's correct.

The current table has three digits in the Code field:

Location Code
Madrid A900

Barcelona

A107
Toledo A201
London B327
Paris C255
Osaka D566

 

Obviously, this table is a small set of a bigger file.

So I need to generate automatically all missing codes for all letters.

E.g. for letter A, I need to generate codes in the load script from A000 to A106, A108 to A200, A202 to A899 and A901 to A999.

Similar for all other letters, even for letter E, which should be generated from E000 to E999 even if there is no Code in the input file with that letter.

 

Kushal_Chawda

@DEMONIO_AZUL  try below. For all not existing codes location will be NULL. If you want some values instead, you can take resident of this table create a column with below condition

if(len(trim(Location))=0,'Not defined',Location) as Location_Final

Data:
Load * Inline [
Location, Code
Madrid, A900
Barcelona, A107
Toledo, A201
London, B327
Paris, C255
Osaka, D566
];

Concatenate(Data)
Load *
where not Exists(Code);
Load Letter & num(From + IterNo()-1,'000') as Code
while IterNo()-1<=To;
Load * Inline [
Letter,From,To
A,0,999
B,0,999
C,0,999
D,0,999
E,0,999 ];

DEMONIO_AZUL
Contributor III
Contributor III
Author

THANK YOU, KUSHAL!
This is exactly what I needed!! Much appreciated!

DEMONIO_AZUL_0-1730208560057.png

 

seanbruton
Luminary Alumni
Luminary Alumni

Superb !!! Neat Solution.